<?php
namespace app\api\controller;
use app\Response;
use QL\QueryList;
use \Curl\Curl;
use think\Db;
use think\Config;
use PHPExcel_IOFactory;
use PHPExcel;

class Excel {
	public function index(){
		$excel_path = Config::get('excel_path');
		$path = isset($excel_path['root_dir'])?$excel_path['root_dir']:'';
		
		$file = $path.'/shenhe.xlsx';
		$total = self::read($file);
		$result = array('共录入数据' => $total.'条');
		Response::show(200,'ok',$result);
	}

	public static function read($file_name){
		$extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));//判断导入表格后缀格式
		if($extension == 'xlsx') {
		$objReader =\PHPExcel_IOFactory::createReader('Excel2007');
		$objPHPExcel =$objReader->load($file_name, $encode = 'utf-8');
		}else if($extension == 'xls'){
		$objReader =\PHPExcel_IOFactory::createReader('Excel5');
		$objPHPExcel =$objReader->load($file_name, $encode = 'utf-8');
		}
		$sheet = $objPHPExcel->getSheet(0);
		$highestRow = $sheet->getHighestRow();//取得总行数
		$highestColumn = $sheet->getHighestColumn(); //取得总列数
		//$arrexcel = $objPHPExcel->getSheet(0)->toarray(0);//excel表数据信息
		$total = 0;
		for($i = 2; $i <= $highestRow; $i++){
			$data = array();
			$data['character'] = $objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue();
			//$data['number'] = $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();
			$data['pinyin'] = $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();
			$data['create_time'] =  date('Y-m-d H:i:s');
			$data['update_time'] =  date('Y-m-d H:i:s');
			//$data['phone'] = $objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue();

			//$de_name = $objPHPExcel->getActiveSheet()->getCell("E".$i)->getValue();

			//$data['password'] = self::getRandomString(8);
			
			$id = self::addData($data);
			if($id){
				$total++;
			}
		}
		return $total;
	}
	public static function addData($data){
		//$id = Db::connect("db_nlsp")->name("tbl_user_reset")->insertGetId($data);
		$id = Db::table('tbl_chinese_pinyin')->insertGetId($data);
		return $id;
	}
	/**
	 * [getRandomString 获取数字和字母的随机组合]
	 * @param  [type] $len   [description]
	 * @param  [type] $chars [description]
	 * @return [type]        [description]
	 */
	public static function getRandomString($len, $chars=null)
	{
	    	if (is_null($chars)){
	        		//$chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
	        		$chars = "abcdefghijklmnopqrstuvwxyz0123456789";
	    	}  
	    	mt_srand(10000000*(double)microtime());
	    	for ($i = 0, $str = '', $lc = strlen($chars)-1; $i < $len; $i++){
	        		$str .= $chars[mt_rand(0, $lc)];  
	    	}
	    	return $str;
	}

}